package info.toegepaste.www.dataaccess;

import info.toegepaste.www.beans.Account;
import java.sql.*;
import java.util.*;
import java.math.*;



public class DAAccount {
private Connection connection = null;

    public DAAccount(String url, String login, String password) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
       
        connection = DriverManager.getConnection(url, login, password);
    }
    public void close() throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }

    public Account createValueObject() {
          return new Account();
    }

    public Account getAccount(int accountID) throws SQLException {

          Account valueObject = createValueObject();
          valueObject.setAccountID(accountID);
          load(valueObject);
          return valueObject;
    }
   


    /**
     * load-method. This will load valueObject contents from database using
     * Primary-Key as identifier. Upper layer should use this so that valueObject
     * instance is created and only primary-key should be specified. Then call
     * this method to complete other persistent information. This method will
     * overwrite all other fields except primary-key and possible runtime variables.
     * If load can not find matching row, NotFoundException will be thrown.
     *
     * @param connection         This method requires working database connection.
     * @param account  This parameter contains the class instance to be loaded.
     *                     Primary-key field must be set for this to work properly.
     */
    public void load(Account account) throws SQLException {

          String sql = "SELECT * FROM account WHERE (accountID = ? ) "; 
          PreparedStatement stmt = null;

          try {
               stmt = connection.prepareStatement(sql);
               stmt.setInt(1, account.getAccountID()); 

               singleQuery(stmt, account);

          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * @param connection         This method requires working database connection.
     */
    public Account checkCredentials(String username, String password){
        Account account = new Account();
                PreparedStatement statement = null;
        ResultSet resultSet = null;
        try{
            statement = connection.prepareStatement("SELECT  * FROM account WHERE loginnaam = ? AND paswoord = ?");
            statement.setString(1, username);
            statement.setString(2,password);
            resultSet = statement.executeQuery();
            if(resultSet.next()){
                account.setAccountID(resultSet.getInt("accountID"));
                account.setLoginnaam(resultSet.getString("loginnaam"));
                account.setPaswoord(resultSet.getString("paswoord"));
                account.setAlumnusID(resultSet.getInt("alumnusID"));
               
                account.setType(resultSet.getString("type"));
            }
            
        } catch(Exception e) {
            e.printStackTrace();
        }  finally {
            try {
                
                statement.close();
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
            }
        }
        
        return account;
    }
    public List getAll() throws SQLException {

          String sql = "SELECT * FROM account ORDER BY accountID ASC ";
          List searchResults = listQuery(connection.prepareStatement(sql));

          return searchResults;
    }



    /**
     * @param connection         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be created.
     *                     If automatic surrogate-keys are not used the Primary-key 
     *                     field must be set for this to work properly.
     */
    public synchronized void create(Account valueObject) throws SQLException {

          String sql = "";
          PreparedStatement stmt = null;
          ResultSet result = null;

          try {
               sql = "INSERT INTO account ( accountID, loginnaam, paswoord, "
               + "alumnusID, type) VALUES (?, ?, ?, ?, ?) ";
               stmt = connection.prepareStatement(sql);

               stmt.setInt(1, valueObject.getAccountID()); 
               stmt.setString(2, valueObject.getLoginnaam()); 
               stmt.setString(3, valueObject.getPaswoord()); 
               stmt.setInt(4, valueObject.getAlumnusID()); 
               stmt.setString(5, valueObject.getType()); 

               int rowcount = databaseUpdate(stmt);
               if (rowcount != 1) {
                    //System.out.println("PrimaryKey Error when updating DB!");
                    throw new SQLException("PrimaryKey Error when updating DB!");
               }

          } finally {
              if (stmt != null)
                  stmt.close();
          }


    }


    /**
     * update-method. This method will update the current state of valueObject to database.
     * Save can not be used to create new instances in database, so upper layer must
     * make sure that the primary-key is correctly specified. Primary-key will indicate
     * which instance is going to be updated in database. If update can not find matching 
     * row, NotFoundException will be thrown.
     *
     * @param connection         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be saved.
     *                     Primary-key field must be set for this to work properly.
     */
    public void update(Account valueObject) 
          throws SQLException {

          String sql = "UPDATE account SET loginnaam = ?, paswoord = ?, alumnusID = ?, "
               + "type = ? WHERE (accountID = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = connection.prepareStatement(sql);
              stmt.setString(1, valueObject.getLoginnaam()); 
              stmt.setString(2, valueObject.getPaswoord()); 
              stmt.setInt(3, valueObject.getAlumnusID()); 
              stmt.setString(4, valueObject.getType()); 

              stmt.setInt(5, valueObject.getAccountID()); 

              int rowcount = databaseUpdate(stmt);
              if (rowcount == 0) {
                   //System.out.println("Object could not be saved! (PrimaryKey not found)");
                 
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
                   
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * delete-method. This method will remove the information from database as identified by
     * by primary-key in supplied valueObject. Once valueObject has been deleted it can not 
     * be restored by calling update. Restoring can only be done using create method but if 
     * database is using automatic surrogate-keys, the resulting object will have different 
     * primary-key than what it was in the deleted object. If delete can not find matching row,
     * NotFoundException will be thrown.
     *
     * @param connection         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance to be deleted.
     *                     Primary-key field must be set for this to work properly.
     */
    public void delete(Account valueObject) 
          throws SQLException {

          String sql = "DELETE FROM account WHERE (accountID = ? ) ";
          PreparedStatement stmt = null;

          try {
              stmt = connection.prepareStatement(sql);
              stmt.setInt(1, valueObject.getAccountID()); 

              int rowcount = databaseUpdate(stmt);
              if (rowcount == 0) {
                   System.out.println("Object could not be deleted (PrimaryKey not found)");
                   
              }
              if (rowcount > 1) {
                   //System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
                   throw new SQLException("PrimaryKey Error when updating DB! (Many objects were deleted!)");
              }
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }
    public void deleteAccount(int alumnusid) {
        String sql = "DELETE FROM account WHERE alumnusID = ?";
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setInt(1, alumnusid);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /**
     * deleteAll-method. This method will remove all information from the table that matches
     * this Dao and ValueObject couple. This should be the most efficient way to clear table.
     * Once deleteAll has been called, no valueObject that has been created before can be 
     * restored by calling update. Restoring can only be done using create method but if database 
     * is using automatic surrogate-keys, the resulting object will have different primary-key 
     * than what it was in the deleted object. (Note, the implementation of this method should
     * be different with different DB backends.)
     *
     * @param connection         This method requires working database connection.
     */
    public void deleteAll(Connection connection) throws SQLException {

          String sql = "DELETE FROM account";
          PreparedStatement stmt = null;

          try {
              stmt = connection.prepareStatement(sql);
              int rowcount = databaseUpdate(stmt);
          } finally {
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * coutAll-method. This method will return the number of all rows from table that matches
     * this Dao. The implementation will simply execute "select count(primarykey) from table".
     * If table is empty, the return value is 0. This method should be used before calling
     * getAll, to make sure table has not too many rows.
     *
     * @param connection         This method requires working database connection.
     */
    public int countAll(Connection connection) throws SQLException {

          String sql = "SELECT count(*) FROM account";
          PreparedStatement stmt = null;
          ResultSet result = null;
          int allRows = 0;

          try {
              stmt = connection.prepareStatement(sql);
              result = stmt.executeQuery();

              if (result.next())
                  allRows = result.getInt(1);
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
          return allRows;
    }


    /** 
     * searchMatching-Method. This method provides searching capability to 
     * get matching valueObjects from database. It works by searching all 
     * objects that match permanent instance variables of given object.
     * Upper layer should use this by setting some parameters in valueObject
     * and then  call searchMatching. The result will be 0-N objects in a List, 
     * all matching those criteria you specified. Those instance-variables that
     * have NULL values are excluded in search-criteria.
     *
     * @param connection         This method requires working database connection.
     * @param valueObject  This parameter contains the class instance where search will be based.
     *                     Primary-key field should not be set.
     */
    public List searchMatching(Account valueObject) throws SQLException {

          List searchResults;

          boolean first = true;
          StringBuffer sql = new StringBuffer("SELECT * FROM account WHERE 1=1 ");

          if (valueObject.getAccountID() != 0) {
              if (first) { first = false; }
              sql.append("AND accountID = ").append(valueObject.getAccountID()).append(" ");
          }

          if (valueObject.getLoginnaam() != null) {
              if (first) { first = false; }
              sql.append("AND loginnaam LIKE '").append(valueObject.getLoginnaam()).append("%' ");
          }

          if (valueObject.getPaswoord() != null) {
              if (first) { first = false; }
              sql.append("AND paswoord LIKE '").append(valueObject.getPaswoord()).append("%' ");
          }

          if (valueObject.getAlumnusID() != 0) {
              if (first) { first = false; }
              sql.append("AND alumnusID = ").append(valueObject.getAlumnusID()).append(" ");
          }

          if (valueObject.getType() != null) {
              if (first) { first = false; }
              sql.append("AND type LIKE '").append(valueObject.getType()).append("%' ");
          }


          sql.append("ORDER BY accountID ASC ");

          // Prevent accidential full table results.
          // Use getAll if all rows must be returned.
          if (first)
               searchResults = new ArrayList();
          else
               searchResults = listQuery(connection.prepareStatement(sql.toString()));

          return searchResults;
    }


    /** 
     * getDaogenVersion will return information about
     * generator which created these sources.
     */
    public String getDaogenVersion() {
        return "DaoGen version 2.4.1";
    }


    /**
     * databaseUpdate-method. This method is a helper method for internal use. It will execute
     * all database handling that will change the information in tables. SELECT queries will
     * not be executed here however. The return value indicates how many rows were affected.
     * This method will also make sure that if cache is used, it will reset when data changes.
     *
     * @param connection         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    protected int databaseUpdate(PreparedStatement stmt) throws SQLException {

          int result = stmt.executeUpdate();

          return result;
    }



    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return only one row. The resultset will be converted
     * to valueObject. If no rows were found, NotFoundException will be thrown.
     *
     * @param connection         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     * @param valueObject  Class-instance where resulting data will be stored.
     */
    protected void singleQuery(PreparedStatement stmt, Account valueObject) 
          throws SQLException {

          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              if (result.next()) {

                   valueObject.setAccountID(result.getInt("accountID")); 
                   valueObject.setLoginnaam(result.getString("loginnaam")); 
                   valueObject.setPaswoord(result.getString("paswoord")); 
                   valueObject.setAlumnusID(result.getInt("alumnusID")); 
                   valueObject.setType(result.getString("type")); 

              } else {
                    //System.out.println("Account Object Not Found!");
                    
              }
          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }
    }


    /**
     * databaseQuery-method. This method is a helper method for internal use. It will execute
     * all database queries that will return multiple rows. The resultset will be converted
     * to the List of valueObjects. If no rows were found, an empty List will be returned.
     *
     * @param connection         This method requires working database connection.
     * @param stmt         This parameter contains the SQL statement to be excuted.
     */
    protected List listQuery(PreparedStatement stmt) throws SQLException {

          ArrayList searchResults = new ArrayList();
          ResultSet result = null;

          try {
              result = stmt.executeQuery();

              while (result.next()) {
                   Account temp = createValueObject();

                   temp.setAccountID(result.getInt("accountID")); 
                   temp.setLoginnaam(result.getString("loginnaam")); 
                   temp.setPaswoord(result.getString("paswoord")); 
                   temp.setAlumnusID(result.getInt("alumnusID")); 
                   temp.setType(result.getString("type")); 

                   searchResults.add(temp);
              }

          } finally {
              if (result != null)
                  result.close();
              if (stmt != null)
                  stmt.close();
          }

          return (List)searchResults;
    }


}